create procedure [dbo].[BAEEventGetCoworkers] @UID VARCHAR(30), @eventCode VARCHAR (10),    @letter varchar(5) AS
    DECLARE @COID varchar(50), @Corec bit

    SELECT @COID = CO_ID, @Corec = COMPANY_RECORD
    FROM Name
    WHERE ID = @UID

    IF @letter = 'ALL'
        SET @letter = ''

    -- if this IS a company record, then get the list by a slightly different method
    IF @Corec = 1
    begin
        SELECT DISTINCT ID, *
        FROM Name AS N
        WHERE ID <> @UID AND N.CO_ID = @UID AND LAST_NAME LIKE @letter + '%' AND ID NOT IN(
            SELECT ST_ID
            FROM Orders AS o
            INNER JOIN Order_Meet AS om ON o.ORDER_NUMBER = om.ORDER_NUMBER
            WHERE MEETING = @eventCode)
        UNION
        SELECT DISTINCT ID, *
        FROM Name AS N
        WHERE ID <> @UID AND N.CO_ID = @UID AND LAST_NAME LIKE @letter + '%' AND ID IN(
            SELECT ST_ID
            FROM Orders AS o
            INNER JOIN Order_Meet AS om ON o.ORDER_NUMBER = om.ORDER_NUMBER
            WHERE MEETING = @eventCode)
        ORDER BY LAST_NAME, FIRST_NAME;
    end
    ELSE
    begin
        SELECT DISTINCT ID,*
        FROM Name AS N
        WHERE ID <> @UID AND (N.CO_ID = @COID OR (N.ID = @COID AND N.COMPANY_RECORD = 1)) AND LEN(LAST_NAME) > 0  AND LAST_NAME LIKE @letter+'%' AND ID NOT IN(
            SELECT ST_ID
            FROM Orders AS o
            INNER JOIN Order_Meet AS om ON o.ORDER_NUMBER = om.ORDER_NUMBER
            WHERE MEETING = @eventCode)
        UNION
        SELECT DISTINCT ID, *
        FROM Name AS N
        WHERE ID <> @UID AND (N.CO_ID = @COID OR (N.ID = @COID AND N.COMPANY_RECORD = 1)) AND LEN(LAST_NAME) > 0 AND LAST_NAME LIKE @letter+'%' AND ID IN(
            SELECT ST_ID
            FROM Orders AS o
            INNER JOIN Order_Meet AS om ON o.ORDER_NUMBER = om.ORDER_NUMBER
            WHERE MEETING = @eventCode)        
        ORDER BY LAST_NAME, FIRST_NAME;
    end

GO